package team.paimengshop.server_database;
import team.paimengshop.server_dataclass.*;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class UserInfoOperate
{
    private static Connection cn = PaiMengShopDatabase.getConnection();

    public UserInfoOperate()
    {

    }

    public static int Add(String userName, String email, String password, String headImageURL, String collection, String cart, String address)
    {
        String sql = "insert into userinfo (userName, email, password, headImageURL, collection, cart, address) values ('" + userName + "','" + email + "','"
                + password + "','" + headImageURL + "','" + collection + "','" + cart + "','" + address +"')";
        int n = -1;

        try
        {
            cn = PaiMengShopDatabase.getConnection();
            PreparedStatement ps = cn.prepareStatement(sql);
            n = ps.executeUpdate();
        } catch (SQLException e)
        {
            e.printStackTrace();
        }
        if (cn != null)
        {
            try
            {
                cn.close();
            } catch (SQLException e)
            {
                e.printStackTrace();
            }
        }
        return n;
    }

    public static int DeleteById(int id)
    {
        String sql = "delete from userinfo where id = ?";
        int n = -1;
        try
        {
            cn = PaiMengShopDatabase.getConnection();
            PreparedStatement ps = cn.prepareStatement(sql);
            ps.setString(1, Integer.toString(id));
            n = ps.executeUpdate();
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
        if (cn != null)
        {
            try
            {
                cn.close();
            } catch (SQLException e)
            {
                e.printStackTrace();
            }
        }
        return n;
    }

    public static int DeleteByUserName(String userName)
    {
        String sql = "delete from userinfo where userName = ?";
        int n = -1;
        try
        {
            cn = PaiMengShopDatabase.getConnection();
            PreparedStatement ps = cn.prepareStatement(sql);
            ps.setString(1, userName);
            n = ps.executeUpdate();
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
        if (cn != null)
        {
            try
            {
                cn.close();
            } catch (SQLException e)
            {
                e.printStackTrace();
            }
        }
        return n;
    }

    public static int UpdateUserName(int id, String newUserName)
    {
        String sql = "UPDATE userinfo SET userName = ? where id = ?";
        int n = -1;
        try
        {
            cn = PaiMengShopDatabase.getConnection();
            PreparedStatement ps = cn.prepareStatement(sql);
            ps.setString(1, newUserName);
            ps.setInt(2, id);
            n = ps.executeUpdate();
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
        if (cn != null)
        {
            try
            {
                cn.close();
            } catch (SQLException e)
            {
                e.printStackTrace();
            }
        }
        return n;
    }

    public static int UpdatePassword(int id, String newPassword)
    {
        String sql = "UPDATE userinfo SET password = ? where id = ?";
        int n = -1;
        try
        {
            cn = PaiMengShopDatabase.getConnection();
            PreparedStatement ps = cn.prepareStatement(sql);
            ps.setString(1, newPassword);
            ps.setInt(2, id);
            n = ps.executeUpdate();
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
        if (cn != null)
        {
            try
            {
                cn.close();
            } catch (SQLException e)
            {
                e.printStackTrace();
            }
        }
        return n;
    }

    public static int UpdatePassword(String email, String newPassword)
    {
        String sql = "UPDATE userinfo SET password = ? where email = ?";
        int n = -1;
        try
        {
            cn = PaiMengShopDatabase.getConnection();
            PreparedStatement ps = cn.prepareStatement(sql);
            ps.setString(1, newPassword);
            ps.setString(2, email);
            n = ps.executeUpdate();
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
        if (cn != null)
        {
            try
            {
                cn.close();
            } catch (SQLException e)
            {
                e.printStackTrace();
            }
        }
        return n;
    }

    public static int UpdateHeadImageURL(int id, String headImageURL)
    {
        String sql = "UPDATE userinfo SET headImageURL = ? where id = ?";
        int n = -1;
        try
        {
            cn = PaiMengShopDatabase.getConnection();
            PreparedStatement ps = cn.prepareStatement(sql);
            ps.setString(1, headImageURL);
            ps.setInt(2, id);
            n = ps.executeUpdate();
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
        if (cn != null)
        {
            try
            {
                cn.close();
            } catch (SQLException e)
            {
                e.printStackTrace();
            }
        }
        return n;
    }

    public static int UpdateAddress(int id, String address)
    {
        String sql = "UPDATE userinfo SET address = ? where id = ?";
        int n = -1;
        try
        {
            cn = PaiMengShopDatabase.getConnection();
            PreparedStatement ps = cn.prepareStatement(sql);
            ps.setString(1, address);
            ps.setInt(2, id);
            n = ps.executeUpdate();
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
        if (cn != null)
        {
            try
            {
                cn.close();
            } catch (SQLException e)
            {
                e.printStackTrace();
            }
        }
        return n;
    }

    public static int UpdateCart(int id, String commodityList)
    {

        String sql = "UPDATE userinfo SET cart = ? where id = ?";
        int n = -1;
        try
        {
            cn = PaiMengShopDatabase.getConnection();
            PreparedStatement ps = cn.prepareStatement(sql);
            ps.setString(1, commodityList);
            ps.setInt(2, id);
            n = ps.executeUpdate();
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
        if (cn != null)
        {
            try
            {
                cn.close();
            } catch (SQLException e)
            {
                e.printStackTrace();
            }
        }
        return n;
    }

    public static int UpdateCollection(int id, String commodityList)
    {
        String sql = "UPDATE userinfo SET collection = ? where id = ?";
        int n = -1;
        try
        {
            cn = PaiMengShopDatabase.getConnection();
            PreparedStatement ps = cn.prepareStatement(sql);
            ps.setString(1, commodityList);
            ps.setInt(2, id);
            n = ps.executeUpdate();
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
        if (cn != null)
        {
            try
            {
                cn.close();
            } catch (SQLException e)
            {
                e.printStackTrace();
            }
        }
        return n;
    }

    public static String GetCartById(int id)
    {
        String cartCommodityList = "";
        String sql = "select * from userinfo where id = ?";
        try
        {
            cn = PaiMengShopDatabase.getConnection();
            PreparedStatement ps = cn.prepareStatement(sql);
            ps.setInt(1, id);
            ResultSet rs = ps.executeQuery();
            while(rs.next())
            {
                cartCommodityList = rs.getString(7);
            }
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
        if (cn != null)
        {
            try
            {
                cn.close();
            } catch (SQLException e)
            {
                e.printStackTrace();
            }
        }
        return cartCommodityList;
    }

    public static int GetIdByEmail(String email)
    {
        int id = 0;
        String sql = "select * from userinfo where email = ?";
        try
        {
            cn = PaiMengShopDatabase.getConnection();
            PreparedStatement ps = cn.prepareStatement(sql);
            ps.setString(1, email);
            ResultSet rs = ps.executeQuery();
            while(rs.next())
            {
                id = rs.getInt(1);
            }
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
        if (cn != null)
        {
            try
            {
                cn.close();
            } catch (SQLException e)
            {
                e.printStackTrace();
            }
        }
        return id;
    }

    public static String GetCollectionById(int id)
    {
        String collectionCommodityList = "";
        String sql = "select * from userinfo where id = ?";
        try
        {
            cn = PaiMengShopDatabase.getConnection();
            PreparedStatement ps = cn.prepareStatement(sql);
            ps.setInt(1, id);
            ResultSet rs = ps.executeQuery();
            while(rs.next())
            {
                collectionCommodityList = rs.getString(6);
            }
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
        if (cn != null)
        {
            try
            {
                cn.close();
            } catch (SQLException e)
            {
                e.printStackTrace();
            }
        }
        return collectionCommodityList;
    }

    public static User FindByEmail(String email)
    {
        User user = new User();
        String sql = "select * from userinfo where email = ?";
        try
        {
            cn = PaiMengShopDatabase.getConnection();
            PreparedStatement ps = cn.prepareStatement(sql);
            ps.setString(1, email);
            ResultSet rs = ps.executeQuery();
            while(rs.next())
            {
                user.setId(rs.getInt(1));
                user.setUserName(rs.getString(2));
                user.setEmail(rs.getString(3));
                user.setPassword(rs.getString(4));
                user.setHeadImageURL(rs.getString(5));
                String collection = rs.getString(6);
                user.setCollection(Commodity.getCommodityArrayListFromString(collection));
                String cart = rs.getString(7);
                user.setCart(Commodity.getCommodityArrayListFromString(cart));
                user.setAddress(rs.getString(8));
            }
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
        if (cn != null)
        {
            try
            {
                cn.close();
            } catch (SQLException e)
            {
                e.printStackTrace();
            }
        }
        return user;
    }

    public static User FindById(int id)
    {
        User user = new User();
        String sql = "select * from userinfo where id = ?";
        try
        {
            cn = PaiMengShopDatabase.getConnection();
            PreparedStatement ps = cn.prepareStatement(sql);
            ps.setInt(1, id);
            ResultSet rs = ps.executeQuery();
            while(rs.next())
            {
                user.setId(rs.getInt(1));
                user.setUserName(rs.getString(2));
                user.setEmail(rs.getString(3));
                user.setPassword(rs.getString(4));
                user.setHeadImageURL(rs.getString(5));
                String collection = rs.getString(6);
                user.setCollection(Commodity.getCommodityArrayListFromString(collection));
                String cart = rs.getString(7);
                user.setCart(Commodity.getCommodityArrayListFromString(cart));
                user.setAddress(rs.getString(8));
            }
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
        if (cn != null)
        {
            try
            {
                cn.close();
            } catch (SQLException e)
            {
                e.printStackTrace();
            }
        }
        return user;
    }

    public static ArrayList<User> GetAllUser()
    {
        ArrayList<User> userArrayList = new ArrayList<>();
        String sql = "select * from userInfo";
        try
        {
            cn = PaiMengShopDatabase.getConnection();
            PreparedStatement ps = cn.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            while(rs.next())
            {
                User user = new User();
                user.setId(rs.getInt(1));
                user.setUserName(rs.getString(2));
                user.setEmail(rs.getString(3));
                user.setPassword(rs.getString(4));
                user.setHeadImageURL(rs.getString(5));
                String collection = rs.getString(6);
                user.setCollection(Commodity.getCommodityArrayListFromString(collection));
                String cart = rs.getString(7);
                user.setCart(Commodity.getCommodityArrayListFromString(cart));
                user.setAddress(rs.getString(8));
                userArrayList.add(user);
            }
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
        if (cn != null)
        {
            try
            {
                cn.close();
            } catch (SQLException e)
            {
                e.printStackTrace();
            }
        }
        return userArrayList;
    }
}
